﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using DTO;
using DAO;

namespace BUS
{
    public class TaiKhoanDAO
    {
        //Tim tk dua tren tkDto
        public static TaiKhoanDTO TimTaiKhoan(string maTK)
        {
            SqlConnection cnn = KetNoiCSDL.TaoKetNoi();
            string query = String.Format("select * from Tai_Khoan where MaTK = '{0}' and (TinhTrang = N'Mở' or (TinhTrang = N'Phong Tỏa' and dbo.TinhSoTienCoHieuLuc({0}) > 0))", maTK);
            SqlCommand cmd = new SqlCommand(query, cnn);

            cnn.Open();

            try
            {
                SqlDataReader dr = cmd.ExecuteReader();
                TaiKhoanDTO taiKhoanDto = new TaiKhoanDTO();
                if (dr.Read())
                {
                    taiKhoanDto.MaTK = dr["MaTK"].ToString();
                    taiKhoanDto.MaLoaiTK = (int)dr["MaLoaiTK"];
                    taiKhoanDto.MaKH = (int)dr["MaKH"];
                    taiKhoanDto.NgayBatDau = (DateTime)dr["NgayBatDau"];
                    taiKhoanDto.NgayKetThuc = (DateTime)dr["NgayKetThuc"];
                    taiKhoanDto.SoDuCo = (double.Parse(dr["SoDuCo"].ToString()));
                    taiKhoanDto.SoDuNo = (double.Parse(dr["SoDuNo"].ToString()));
                    taiKhoanDto.SoDu = (double.Parse(dr["SoDu"].ToString()));
                    taiKhoanDto.TinhTrang = dr["TinhTrang"].ToString();
                    taiKhoanDto.MaChiNhanh = (int)dr["MaChiNhanh"];
                    taiKhoanDto.LoaiTienTe = (int)dr["LoaiTienTe"];
                }
                cnn.Close();
                return taiKhoanDto;
            }
            catch (Exception ex)
            {
                if (cnn.State == ConnectionState.Open)
                    cnn.Close();
                throw ex; 
            }
        }

        //tim TK dua tren khDto
        // 0712455
        public static List<TaiKhoanDTO> TimDSTaiKhoan(List<KhachHangDTO> lstKH)
        {
            
            SqlConnection cnn = KetNoiCSDL.TaoKetNoi();
            List<TaiKhoanDTO> lsttaiKhoanDto = new List<TaiKhoanDTO>(); ;

            for (int i = 0; i < lstKH.Count; i++)
            {
                string query = String.Format("select * from Tai_Khoan where MaKH = '{0}' and (TinhTrang = N'Mở' or (TinhTrang = N'Phong Tỏa' and dbo.TinhSoTienCoHieuLuc(MaTK) > 0))", lstKH[i].MaKH);
                SqlCommand cmd = new SqlCommand(query, cnn);

                cnn.Open();
                try
                {
                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        TaiKhoanDTO taiKhoanDto = new TaiKhoanDTO();

                        taiKhoanDto.MaTK = dr["MaTK"].ToString();
                        taiKhoanDto.MaLoaiTK = (int)dr["MaLoaiTK"];
                        taiKhoanDto.MaKH = (int)dr["MaKH"];
                        taiKhoanDto.NgayBatDau = (DateTime)dr["NgayBatDau"];
                        taiKhoanDto.NgayKetThuc = (DateTime)dr["NgayKetThuc"];
                        taiKhoanDto.SoDuCo = Convert.ToDouble(dr["SoDuCo"].ToString());
                        taiKhoanDto.SoDuNo = Convert.ToDouble(dr["SoDuNo"].ToString());
                        taiKhoanDto.SoDu = Convert.ToDouble(dr["SoDu"].ToString());
                        taiKhoanDto.TinhTrang = dr["TinhTrang"].ToString();
                        taiKhoanDto.MaChiNhanh = (int)dr["MaChiNhanh"];
                        taiKhoanDto.LoaiTienTe = (int)dr["LoaiTienTe"];

                        lsttaiKhoanDto.Add(taiKhoanDto);
                    }
                    cnn.Close();
                }
                catch (Exception ex)
                {
                    if (cnn.State == ConnectionState.Open)
                        cnn.Close();
                    throw ex;
                }
          }
          return lsttaiKhoanDto;
        }

        // end 0712455

        //0712467
        public static int DongTaiKhoan(TaiKhoanDTO tkDto, string recentUser)
        {
            SqlConnection cnn = KetNoiCSDL.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("sp_DongTK", cnn);

            cmd.CommandType = CommandType.StoredProcedure ;

            cmd.Parameters.Add("@MaTK", SqlDbType.Char);
            cmd.Parameters.Add("@MaNVGD", SqlDbType.Char);
            cmd.Parameters.Add("@ChiNhanhGD", SqlDbType.Int);

            cmd.Parameters["@MaTK"].Value = tkDto.MaTK;
            cmd.Parameters["@MaNVGD"].Value = recentUser;
            cmd.Parameters["@ChiNhanhGD"].Value = NhanVienDAO.LayChiNhanhGD(recentUser);

            cnn.Open();
            int rs = 0;
            try
            {
                rs = cmd.ExecuteNonQuery();
                cnn.Close();
                return rs;
            }
            catch (Exception ex)
            {
                if (cnn.State == ConnectionState.Open)
                    cnn.Close();
                throw ex;
            }            
        }

        public static int PhongToaTaiKhoan(TaiKhoanDTO tkDto, PhongToaTKDTO ptDto, string recentUser)
        {
            SqlConnection cnn = KetNoiCSDL.TaoKetNoi();
            SqlCommand cmd = new SqlCommand("sp_PhongToaTK", cnn);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@MaTK", SqlDbType.Char);

            cmd.Parameters.Add("@SoTienPT", SqlDbType.Money);
            cmd.Parameters.Add("@NgayBatDauPT", SqlDbType.DateTime);
            cmd.Parameters.Add("@NgayKetThucPT", SqlDbType.DateTime);
            cmd.Parameters.Add("@MaNVGD", SqlDbType.Char);
            cmd.Parameters.Add("ChiNhanhGD", SqlDbType.Int);


            cmd.Parameters["@MaTK"].Value = tkDto.MaTK;

            cmd.Parameters["@SoTienPT"].Value = ptDto.SoTienPT;
            cmd.Parameters["@NgayBatDauPT"].Value = ptDto.NgayBatDauPT;
            cmd.Parameters["@NgayKetThucPT"].Value = ptDto.NgayKetThucPT;
            cmd.Parameters["@MaNVGD"].Value = recentUser;
            cmd.Parameters["@ChiNhanhGD"].Value = NhanVienDAO.LayChiNhanhGD(recentUser);

            cnn.Open();
            int rs = 0;
            try
            {
                rs = cmd.ExecuteNonQuery();
                cnn.Close();
                return rs;
            }
            catch (Exception ex)
            {
                if (cnn.State == ConnectionState.Open)
                    cnn.Close();
                throw ex;
            }
        }     
    }
}
